package dbLayer;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import modelLayer.Block;
import modelLayer.Schedule;
import modelLayer.Class;

/**
 * This class is a database layer class of the MSAS system. It manipulates database
 * information of schedule objects. This class implements its interface, and have therefore
 * no direct connection to the control layer.
 * 
 * @author Danny M�lgaard, Anna Kelemen, Jakob Korsgaard
 * 		   
 * @version 1.3.1
 * 					1.3.1: 		Added more extensive documentation. Verified that
 * 								information contained in the Design Class Diagram,
 * 								is in accordance with the information contained in this
 * 								class.
 */
public class DBSchedule implements DBScheduleIF {
	
	private Connection dbConnection;

	public DBSchedule(){
		
		this.dbConnection = DBConnection.getInstance().getDBcon();

	}

	@Override
	public int insert(String clName, int numberOfBlocks) throws SQLException {
		
		int rc = -1;
		String query = "INSERT INTO Schedule VALUES (?, ?)";
		
		try {
			PreparedStatement ps = this.dbConnection.prepareStatement(query, Statement.RETURN_GENERATED_KEYS);
			ps.setInt(1, new DBClass().find(clName, false).getId());
			ps.setInt(2, numberOfBlocks);
			rc = ps.executeUpdate();			
			if (rc > 0){
				ResultSet rs = ps.getGeneratedKeys();
				if (rs.next()){
					int id = rs.getInt(1);
					new DBBlock().insert(id, numberOfBlocks);
				}
			}		
			ps.close();
		}
		catch (SQLException e){
			System.out.println("DEBUG MESSAGE [SCHEDULE]: insert(): " + e);
			throw new SQLException("");
		}
		
		return rc;
		
	}

	@Override
	public Schedule find(String clName, boolean retriveAssociation) {

		String wClause = "class = '" + (new DBClass().find(clName, false).getId()) + "'";
		return singleWhere(wClause, retriveAssociation);

	}

	@Override
	public Schedule findById(int scheduleId, boolean retriveAssociation) {

		String wClause = "id = '" + scheduleId + "'";
		return singleWhere(wClause, retriveAssociation);

	}

	@Override
	public List<Schedule> getAll(boolean retriveAssociation) {
	
		String wClause = "";
		return miscWhere(wClause, retriveAssociation);

	}

	@Override
	public int update(String className, int tbId, int blockNo, int scheduleId) {
		
		int rc = -1;

		try {
			DBTeachingBlock dbTeachingBlock = new DBTeachingBlock();
			DBBlock dbBlock = new DBBlock();
			rc = dbBlock.update(scheduleId, blockNo, dbTeachingBlock.findById(tbId, false));
		}
		catch (Exception e){
			System.out.println("DEBUG MESSAGE [SCHEDULE]: update(): " + e);
		}
	
		return rc;

	}

	@Override
	public int remove(String className) {

		int rc = -1;
		
		DBBlock dbBlock = new DBBlock();
		int id = this.find(className, false).getId();
		
		for (int i = 1; i <= 36; ++i) {
			try {
				dbBlock.remove(id, i);
			}
			catch (Exception e) {
				System.out.println("DEBUG MESSAGE [SCHEDULE]: remove(): " + e);
			}
		}
		
		String query = "DELETE FROM Schedule WHERE id = ?";
		try {
			PreparedStatement ps = this.dbConnection.prepareStatement(query);
			ps.setInt(1, id);
			rc = ps.executeUpdate();
			ps.close();
		}
		catch (Exception e){
			System.out.println("DEBUG MESSAGE [SCHEDULE]: remove(): " + e);
		}
		
		return rc;
	}
	
	/**
	 * Builds a query with a where clause (condition), and returns a single object
	 * to the caller. This method is used when only wanting one object built from 
	 * the database. This method takes the following parameters:
	 *  
	 * @param 	wClause				String		the search condition, and where clause of the SQL query.
	 * @param 	retriveAssociation	boolean		states if we're to retrieve associations in the object aswell.
	 * @return	sch					Schedule	a built Schedule object, based on query results from the database.
	 */
	private Schedule singleWhere(String wClause, boolean retriveAssociation) {
		
		ResultSet rs;
		Schedule sch = null;
		String query = builQuery(wClause);
		
		try {
			Statement stmt = this.dbConnection.createStatement();
			stmt.setQueryTimeout(5);
			rs = stmt.executeQuery(query);
			if (rs.next()) {
				sch = buildObject(rs);
				if (retriveAssociation){
					String clName = sch.getCl().getClassName();
					Class cl = new DBClass().find(clName, retriveAssociation);
					sch.setCl(cl);
					
					List<Block> bList = new ArrayList<>();
					int schId = sch.getId();
					bList = new DBBlock().getAllBySchedule(schId, retriveAssociation);
					sch.setBlocks(bList);
				}
			}
			stmt.close();
		}
		catch (Exception e){
			System.out.println("DEBUG MESSAGE [SCHEDULE]: singleWhere(): " + e);
		}
		
		return sch;
		
	}

	/**
	 * Builds a query with a where clause (condition), and returns a collection of objects
	 * to the caller. This method is used when only wanting one or more objects built from 
	 * the database. This method takes the following parameters:
	 *  
	 * @param 	wClause				String				the search condition, and where clause of the SQL query.
	 * @param 	retriveAssociation	boolean				states if we're to retrieve associations in the object aswell.
	 * @return	list				List<Schedule>		a list of built Schedule objects, based on query results from the 
	 * 													database.
	 */
	private List<Schedule> miscWhere(String wClause, boolean retriveAssociation) {
		
		List<Schedule> list = new ArrayList<>();
		ResultSet rs;
		String query = builQuery(wClause);
		
		try {
			Statement stmt = this.dbConnection.createStatement();
			stmt.setQueryTimeout(5);
			rs = stmt.executeQuery(query);
			while (rs.next()){
				Schedule sch = new Schedule();
				sch = buildObject(rs);
				list.add(sch);
			}		
			if (retriveAssociation) {
				for (Schedule sch : list){
					String clName = sch.getCl().getClassName();
					Class cl = new DBClass().find(clName, retriveAssociation);
					sch.setCl(cl);
					
					List<Block> bList = new ArrayList<>();
					int schId = sch.getId();
					bList = new DBBlock().getAllBySchedule(schId, retriveAssociation);
					sch.setBlocks(bList);
				}
			}
			stmt.close();
		}
		catch (Exception e){
			System.out.println("DEBUG MESSAGE [SCHEDULE]: miscWhere(): " + e.getMessage());
		}
		
		return list;
		
	}
	
	/**
	 * Builds a query with a where clause (condition), and returns a string to the caller.
	 * This method takes the following parameters:
	 *  
	 * @param 	wClause				String		the search condition, and where clause of the SQL query.
	 * @return	query				String		an SQL query as a String.
	 */
	private String builQuery(String wClause) {
		
		String query = "SELECT id, class, blocks FROM Schedule";
		if (wClause.length() > 0) {
			query = query + " WHERE " + wClause;
		}
		
		return query;
		
	}

	/**
	 * builds an object of type Schedule based on the resultset of the SQL query. Returns this
	 * object to the caller.
	 * 
	 * @param 	rs		ResultSet		the resultset from the SQL query.
	 * @return	sch		Schedule		the built Schedule object.
	 */
	private Schedule buildObject(ResultSet rs) {
		
		Schedule sch = new Schedule();
		List<Block> bList = new ArrayList<Block>();
		
		try {
			sch.setId(rs.getInt("id"));
			sch.setCl(new DBClass().findById(rs.getInt("class"), false));
			sch.setNumberOfBlocks(rs.getInt("blocks"));
			//bList = new DBBlock().getAllBySchedule(sch.getId(), false);
			sch.setBlocks(bList);
		}
		
		catch (Exception e){
			System.out.println("DEBUG MESSAGE [SCHEDULE]: buildObject(). Reason: " + e);
		}
		
		return sch;
	
	}

}
